Homework 4
Python selenium, APIs, & Pandas Basics
Direction
Please submit your Python Script for Part 1 and Part 2 in Homework 4 to Brightspace with the name below:
danl_210_hw4_LASTNAME_FIRSTNAME.py
( e.g.,danl_210_hw4_choe_byeonghak.py)
The due is April 18, 2025, 10:30 A.M.
Please send Prof. Choe an email (bchoe@geneseo.edu) if you have any questions.
Part 1. Data Collection
Question 1
Go to the following webpage: https://www.nyc.gov/site/finance/property/property-annualized-sales-update.page
Provide a Python Selenium code to download all the MS Excel files in the webpage above.
- Please make sure that you do not download any PDF files.
Answer:
Question 2
The series_housing_price DataFrame contains FRED series ids for the Home Price Index across 16 metropolitan statistical areas, each categorized into three price tiers: low, middle, and high.
url = 'https://bcdanl.github.io/data/fred_api_series_housing_price.csv'
series_housing_price = pd.read_csv(url)For more information regarding the index, please visit Standard & Poor’s. There is more information about home price sales pairs in the Methodology section. Copyright, 2016, Standard & Poor’s Financial Services LLC.
Provide a Python code (excluding your FRED API key) to construct the following DataFrame:
Note that the
idvariable in theseries_housing_priceDataFrame corresponds to theseries_idkey used in the parameter dictionary (param_dicts) used for making requests to the FRED API.To iterate over the
idvalues in theseries_housing_priceDataFrame, you can convert the variable to a list using.tolist():
for val in series_housing_price['id'].tolist():
# do something with val- Below replaces ‘Home Price Index (’ with ’’ (empty string) in the
titlecolumn:
df_all['title'] = df_all['title'].str.replace('Home Price Index (', '')- The
titlecolumn can split into thetierandcityvariables using the following code:
# Split the 'title' column into two new variables, 'ranking' and 'title'
# by the first occurrence of " Tier) for ".
# expand=True tells pandas to return the results in a DataFrame
# If expand were False, the result would be a Series of lists.
# Use \) to escape the closing parenthesis, which has special meaning in regex
df_all[['tier', 'city']] = df_all['title'].str.split(' Tier\) for ', expand=True) - The DataFrame is sorted first by
state, then bycitywithin eachstate, then bydatein descending order within eachcity, and finally bytierwithin eachcity-dategroup.- To sort observations in a custom order (like Low, Middle, High), you can convert the
tiervariable to a categorical variable with an explicit order, and then sort.
- To sort observations in a custom order (like Low, Middle, High), you can convert the
df = pd.DataFrame({
'tier': ['Middle', 'High', 'Low', 'Middle', 'Low'],
'city': ['NY', 'LA', 'CHI', 'SF', 'SEA']
})
tier_order = ['Low', 'Middle', 'High']
df['tier'] = pd.Categorical(df['tier'], categories=tier_order, ordered=True)
df_sorted = df.sort_values('tier')Answer:
Part 2. Pandas Basics
import pandas as pd
import numpy as npThe following describes the context of DataFrame in Part 2.
Open Payments, which is managed by the Centers for Medicare & Medicaid Services (CMS), is a national disclosure program created by the Affordable Care Act (ACA). The program promotes transparency and accountability by helping consumers understand the financial relationships between pharmaceutical and medical device industries, physicians, and non-physician practitioners (NPP). Non-physician practitioners (NPPs) collectively refers to physician assistants, nurse practitioners, clinical nurse specialists, certified registered nurse anesthetists/anesthesiologists and certified nurse-midwives provider types.
These financial relationships may include consulting fees, research grants, travel reimbursements, and payments made from the industry to medical practitioners. It is important to note that financial ties between the health care industry and health care providers do not necessarily indicate an improper relationship.
- Let us consider the CMS’ Open Payments data for selected cities in New York state in year 2022, the
cms_ny_2022DataFrame:
cms_ny_2022 = pd.read_csv('https://bcdanl.github.io/data/cms-2022-cities-all.zip')Variable Description
| variable | type | description |
|---|---|---|
Record_ID |
numeric | Unique identifier for a transaction between a manufacturer and a physician or an NPP |
Month |
numeric | Month |
Day |
numeric | Year |
Amount_of_Payment |
numeric | US dollar amount of payment to a physician or NPP |
Product |
chracter | An indicator showing if the product is a Drug, Device, Biological, or Medical Supply. |
Product_Manufacturer |
chracter | Manufacturer making the payment to a physician/NPP |
NPI |
numeric | National Provider Identifier, a unique identification number for a physician/NPP receiving the payment |
Physician_or_NPP |
chracter | An indicator showing if the recipient of the payment is a physician or non-physician practitioner (NPP) |
First_Name |
chracter | First name of physician/NPP in New York state |
Last_Name |
chracter | Last name of physician/NPP in New York state |
City |
chracter | City of the physician/NPP in New York state |
Type |
chracter | Type of physician/NPP (e.g., Medical Doctor, Doctor of Optometry, Nurse Practitioner, Physician Assistant, and more) |
Taxonomy |
chracter | Standardized taxonomy of medical service provider (e.g., Allopathic & Osteopathic Physicians, Chiropractic Providers, Dental Providers, Physician Assistants & Advanced Practice Nursing Providers, and more) |
Specialty |
chracter | A specialty chosen within the standardized taxonomy (e.g., Psychiatry & Neurology, Physician Assistant, Chiropractor, Dentist, Family Medicine, Neurological Surgery, and more) |
Specialty_Detail |
chracter | Detail in the specialty if any (e.g., Family, Gastroenterology, Cardiovascular Disease, Neurology, Psychiatry, and more) |
- Note that each physician/NPP corresponds to one single
Specialty_Detailvalue.
Question 3
- Write a Python code to convert the string type
Physician_or_NPPvariable into the category type variable.
Question 4
- Write a Python code to replace the NaN value of the
Specialty_Detailvariable with the value of theSpecialtyvariable.- For example, the following shows that
- All NaN values in the
Specialty_Detailvariable, where theSpecialtyis Physician Assistant, are replaced with Physician Assistant.
- All NaN values in the
- For example, the following shows that
Answer:
Question 5
Write a Python code to count the number of transactions in which the payment amount (i.e., Amount_of_Payment) falls in the top 10% of all payments in the DataFrame.
Answer:
Question 6
Write a Python code to identify the city or cities with the highest number of non-missing values in the Specialty_Detail variable of the cms_ny_2022 DataFrame.
Answer:
Question 7
- Write a Python code to count the number of occurrences of each unique value in
Specialty_Detailfor each unique value inTaxonomy.- For example, the following displays the number of occurrences of each unique in
Specialty_Detailfor the Dental ProvidersTaxonomyvalue.
- For example, the following displays the number of occurrences of each unique in
Answer:
Question 8
- Write a Python code to count the number of unique
Specialty_Detailvalues for the value “Dental Providers” inTaxonomy.
Answer:
Question 9
- Write a Python code (1) to count the number of missing values and (2) to calculate mean, standard deviation, minimum, first quartile, median, third quartile, and maximum of the
Amount_of_Paymentvariable for the value Dermatology in theSpecialtyvariable.
Answer:
Question 10
- Write a Python code to replicate the following DataFrame, which calculates (1) the number of physicians in each city, (2) the number of NPPs in each city, (3) the number of physicians and NPPs in each city, and (4) the percentage of physicians and NPPs in each city, as shown below.
- Ensure that the sum of the percentages of physicians and NPPs within each city is 100.
- Ensure that the observations are sorted by
Total_CountandCityin descending and ascending orders, respectively.
Answer:
Question 11
- Write a Python code to calculate:
- The total
Amount_of_PaymenttheProduct_ManufacturerAbbVie Inc. paid to physicians and non-physician practitioners (NPPs) inCitiesof Rochester, Buffalo, and Syracuse. - How many times the
Product_ManufacturerAbbVie Inc. paid to physicians and non-physician practitioners (NPPs) in inCitiesof Rochester, Buffalo, and Syracuse.
- The total
Answer:
Question 12
- Consider the following two DataFrames,
cms_ny_2022_npiandcms_ny_2022_records:
cms_ny_2022_npi <- pd.read_csv('https://bcdanl.github.io/data/cms-2022-cities-npi.csv')cms_ny_2022_npi: This DataFrame is created fromcms_ny_2022by selecting only the columnsNPI,Physician_or_NPP,First_Name,Last_Name,City,Type,Taxonomy,Specialty, andSpecialty_Detail.- In addition, it contains only unique observations (i.e., there are no duplicate rows).
cms_ny_2022_records <- pd.read_csv('https://bcdanl.github.io/data/cms-2022-cities-records.csv')cms_ny_2022_records: This DataFrame is derived fromcms_ny_2022by including every observation but only retaining the columnsRecord_ID,Month,Day,Amount_of_Payment,Product,Product_Manufacturer, andNPI.Write a Python code to create the
cms_ny_2022DataFrame by using thecms_ny_2022_npiandcms_ny_2022_recordsDataFrames.
Answer:
Question 13
- Write a Python code to create the following DataFrame,
City_Physician_or_NPP, which counts the number of physicians and the non-physician practitioners (NPPs) in each city.
Answer: